﻿using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace rmTest.Common
{
    public class ExcelUtil
    {

        /// <summary>
        /// 用于excel表格中列号字母转成列索引，从0对应A开始
        /// </summary>
        /// <param name="column">列号</param>
        /// <returns>列索引</returns>
        public static int ColumnToIndex(string column)
        {
            if (!Regex.IsMatch(column.ToUpper(), @"[A-Z]+"))
            {
                throw new Exception("Invalid parameter");
            }
            int index = 0;
            char[] chars = column.ToUpper().ToCharArray();
            for (int i = 0; i < chars.Length; i++)
            {
                index += ((int)chars[i] - (int)'A' + 1) * (int)Math.Pow(26, chars.Length - i - 1);
            }
            return index - 1;
        }

        /// <summary>
        /// 用于将excel表格中列索引转成列号字母，从A对应0开始
        /// </summary>
        /// <param name="index">列索引</param>
        /// <returns>列号</returns>
        public static string IndexToColumn(int index)
        {
            index = index + 1;
            if (index < 0)
            {
                throw new Exception("Invalid parameter");
            }
            index--;
            string column = string.Empty;
            do
            {
                if (column.Length > 0)
                {
                    index--;
                }
                column = ((char)(index % 26 + (int)'A')).ToString() + column;
                index = (int)((index - index % 26) / 26);
            } while (index > 0);
            return column;

        }


        //其他枚举
        public static Hashtable GetEnumData(Type type)
        {
            Hashtable ht = new Hashtable();
            foreach (int myCode in System.Enum.GetValues(type))
            {
                string strName = System.Enum.GetName(type, myCode);//获取名称
                string strVaule = myCode.ToString();//获取值
                if (!ht.ContainsKey(strName))
                {
                    ht.Add(strName, strVaule);
                }
            }
            return ht;
        }

        //其他枚举（排序用）
        private Hashtable GetEnumData2(Type type)
        {
            Hashtable ht = new Hashtable();
            foreach (int myCode in System.Enum.GetValues(type))
            {
                string strName = System.Enum.GetName(type, myCode);//获取名称
                string strVaule = myCode.ToString();//获取值
                if (!ht.ContainsKey(strName))
                {
                    ht.Add(strVaule, strName);
                }
            }
            return ht;
        }

        /// <summary>
        /// 设置数据源(按照值排序)
        /// </summary>
        /// <param name="datasourcename">源选项名称。例如：所属单位</param>
        /// <param name="datasourcename">数据源</param>
        /// <param name="colindex">填充到数据源sheet中的列号</param>
        public static int SetCellDataSource2Sort(HSSFWorkbook hssfworkbook, string datasourcename, Hashtable ht, int colindex, string sheetname)
        {
            //任务类型标题
            ISheet Sheet2 = hssfworkbook.GetSheet(sheetname);
            if (Sheet2 == null)
            {
                Sheet2 = hssfworkbook.CreateSheet();
            }
            //源选项名称。例如：所属单位
            IRow row = Sheet2.GetRow(0);
            if (row == null)
            {
                row = Sheet2.CreateRow(0);
            }
            row.CreateCell(colindex).SetCellValue(datasourcename);
            //添加空白项。
            IRow row2 = Sheet2.GetRow(1);
            if (row2 == null)
            {
                row2 = Sheet2.CreateRow(1);
            }
            row2.CreateCell(colindex).SetCellValue("");
            int i = 2;
            ArrayList alv = new ArrayList(ht.Keys);
            alv.Sort();
            foreach (object obj in alv)
            {
                IRow curRow = Sheet2.GetRow(i);
                if (curRow == null)
                {
                    curRow = Sheet2.CreateRow(i);
                }
                curRow.CreateCell(colindex).SetCellValue(ht[obj].ToString());
                i++;
            }

            return ht.Count + 2;
        }

        /// <summary>
        /// 设置数据源
        /// </summary>
        /// <param name="datasourcename">源选项名称。例如：所属单位</param>
        /// <param name="datasourcename">数据源</param>
        /// <param name="colindex">填充到数据源sheet中的列号</param>
        public static int SetCellDataSource(HSSFWorkbook hssfworkbook, string datasourcename, Hashtable ht, int colindex, string sheetname)
        {
            //任务类型标题
            ISheet Sheet2 = hssfworkbook.GetSheet(sheetname);
            if (Sheet2 == null)
            {
                Sheet2 = hssfworkbook.CreateSheet();
            }
            //源选项名称。例如：所属单位
            IRow row = Sheet2.GetRow(0);
            if (row == null)
            {
                row = Sheet2.CreateRow(0);
            }
            row.CreateCell(colindex).SetCellValue(datasourcename);
            //添加空白项。
            IRow row2 = Sheet2.GetRow(1);
            if (row2 == null)
            {
                row2 = Sheet2.CreateRow(1);
            }
            row2.CreateCell(colindex).SetCellValue("");
            int i = 2;
            foreach (object obj in ht.Keys)
            {

                IRow curRow = Sheet2.GetRow(i);
                if (curRow == null)
                {
                    curRow = Sheet2.CreateRow(i);
                }
                curRow.CreateCell(colindex).SetCellValue(obj.ToString());
                i++;
            }
            return ht.Count + 2;
        }

        /// <summary>
        /// 设置选择源区域
        /// </summary>
        /// <param name="sheet">数据区sheet</param>
        /// <param name="colIndex">数据区对应的选择项列</param>
        /// <param name="sSheetName">源sheet</param>
        /// <param name="letter">源对应的列编号名</param>
        /// <param name="startRowIndex">源起始行</param>
        /// <param name="endRowIndex">源结束行</param>
        public static void SetChongeSource(HSSFWorkbook hssfworkbook, ref ISheet sheet, int colIndex, string sSheetName, string letter, int startRowIndex, int endRowIndex, string dicRange)
        {
            CellRangeAddressList rangeList = new CellRangeAddressList(1, 1000, colIndex, colIndex);

            HSSFName range = (HSSFName)hssfworkbook.CreateName();
            range.RefersToFormula = sSheetName + "!$" + letter + "$" + startRowIndex + ":$" + letter + "$" + endRowIndex;
            range.NameName = dicRange;

            DVConstraint dvconstraint = DVConstraint.CreateFormulaListConstraint(dicRange);
            HSSFDataValidation dataValidation = new HSSFDataValidation(rangeList, dvconstraint);
            ((HSSFSheet)sheet).AddValidationData(dataValidation);
        }

        /// <summary>
        /// 初始化源sheet
        /// </summary>
        /// <param name="currentSheet">当前sheet</param>
        /// <param name="sheetIndex">sheet编号</param>
        /// <param name="sSheetName">sheet名称</param>
        public static void InitSheet(HSSFWorkbook hssfworkbook, ref ISheet currentSheet, int sheetIndex, string sSheetName)
        {
            try
            {
                currentSheet = hssfworkbook.GetSheetAt(sheetIndex);
            }
            catch { }
            if (currentSheet != null)
            {
                hssfworkbook.RemoveSheetAt(sheetIndex);
            }
            currentSheet = hssfworkbook.CreateSheet(sSheetName);
            hssfworkbook.SetSheetHidden(sheetIndex, true);
        }

        /// <summary>
        /// 初始化Excel
        /// </summary>
        /// <param name="stream"></param>
        public static HSSFWorkbook InitWorkbook(FileStream stream, string sheetName)
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook(stream);

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "Ins lnc";
            hssfworkbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = sheetName;
            hssfworkbook.SummaryInformation = si;
            return hssfworkbook;
        }

        /// <summary>
        /// Excel转换DataTable
        /// </summary>
        /// <param name="FilePath">文件的绝对路径</param>
        /// <returns>DataTable</returns>
        public static DataSet ExcelInput(string FilePath)
        {
            DataSet ds = new DataSet();

            //根据路径通过已存在的excel来创建HSSFWorkbook，即整个excel文档

            HSSFWorkbook workbook = new HSSFWorkbook(File.Open(FilePath, FileMode.Open));
            for (int tidx = 0; tidx < workbook.Count; tidx++)
            {
                DataTable table = new DataTable();
                HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(tidx);
                table.TableName = sheet.SheetName;
                //获取excel的第一个sheet

                //获取Excel的最大行数
                int rowsCount = sheet.PhysicalNumberOfRows;
                //为保证Table布局与Excel一样，这里应该取所有行中的最大列数（需要遍历整个Sheet）。
                //为少一次全Excel遍历，提高性能，我们可以人为把第0行的列数调整至所有行中的最大列数。
                int colsCount = sheet.GetRow(0) != null ? sheet.GetRow(0).PhysicalNumberOfCells : 0;

                for (int i = 0; i < colsCount; i++)
                {
                    ICell cell = sheet.GetRow(0).GetCell(i);
                    //if (cell != null && !string.IsNullOrEmpty(cell + string.Empty))
                    //{
                    table.Columns.Add(cell + string.Empty);
                    //}
                }

                for (int x = 1; x < rowsCount; x++)
                {
                    DataRow dr = table.NewRow();
                    string empty = string.Empty;
                    if (colsCount >= 1)
                    {
                        for (int y = 0; y < colsCount; y++)
                        {
                            if (sheet.GetRow(x) == null) break;
                            ICell cell = sheet.GetRow(x).GetCell(y);
                            if (cell != null)
                            {
                                //NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
                                if (cell.CellType == CellType.Numeric)
                                {
                                    if (HSSFDateUtil.IsCellDateFormatted(cell))
                                    {
                                        dr[y] = cell.DateCellValue;
                                    }
                                    else
                                    {
                                        dr[y] = cell.NumericCellValue;
                                    }
                                }
                                else if (cell.CellType == CellType.Blank)//空数据类型
                                {
                                    dr[y] = "";
                                }
                                else if (cell.CellType == CellType.Formula)//公式类型
                                {
                                    //HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                                    //e.EvaluateInCell(cell);
                                    //dataRow[j] = cell.ToString();
                                    HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(workbook);
                                    CellValue icell = eva.Evaluate(cell);
                                    if (icell.CellType == CellType.Numeric)
                                    {
                                        dr[y] = icell.NumberValue;
                                    }
                                    else if (icell.CellType == CellType.Blank)
                                    {
                                        dr[y] = icell.StringValue;
                                    }
                                    else if (icell.CellType == CellType.Boolean)
                                    {
                                        dr[y] = icell.BooleanValue;
                                    }
                                }
                                else
                                {
                                    dr[y] = cell.StringCellValue;
                                }
                            }
                            else
                                dr[y] = null;
                            empty += dr[y];
                        }
                    }
                    if (!string.IsNullOrEmpty(empty))
                    {
                        table.Rows.Add(dr);
                    }
                }
                ds.Tables.Add(table);
            }
            return ds;
        }
    }
}
